﻿--
-- Copyright 2012-2013 Mohawk College of Applied Arts and Technology
-- 
-- Licensed under the Apache License, Version 2.0 (the "License"); you 
-- may not use this file except in compliance with the License. You may 
-- obtain a copy of the License at 
-- 
-- http://www.apache.org/licenses/LICENSE-2.0 
-- 
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
-- WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the 
-- License for the specific language governing permissions and limitations under 
-- the License.
-- 
-- User: fyfej
-- Date: 5-12-2012
--

CREATE OR REPLACE FUNCTION make_plpgsql()
RETURNS VOID
LANGUAGE SQL
AS $$
CREATE LANGUAGE plpgsql;
$$;

SELECT
    CASE
    WHEN EXISTS(
        SELECT 1
        FROM pg_catalog.pg_language
        WHERE lanname='plpgsql'
    )
    THEN NULL
    ELSE make_plpgsql() END;
 
DROP FUNCTION make_plpgsql();

-- CORRECT BYTEA PROBLEM
SET bytea_output = ESCAPE;


--DROP TABLE MSG_TBL CASCADE;

 
 -- @TABLE
 -- MAIN MESSAGE STORAGE TABLE
 --
 -- REMARKS: RESPONSIBLE FOR THE STORAGE AND RETRIEVAL OF REQUESTS AND RESPONSES TO MESSAGES
 CREATE TABLE MSG_TBL
 (
	MSG_ID		VARCHAR(96) NOT NULL, -- GUID OF THE MESSAGE BEING STORED
	MSG_RSP_ID	VARCHAR(96), -- GUID OF THE MESSAGE ID THAT THIS MESSAGE RESPONDS TO
	MSG_UTC		TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- THE TIME THAT THE MESSAGE WAS CREATED IN THE DATABASE
	MSG_SRC		VARCHAR(256), -- MESSAGE SOURCE
	MSG_DST		VARCHAR(256), -- MESSAGE DESTINATION
	MSG_BODY	BYTEA NOT NULL, -- CONTENT OF THE ACTUAL MESSAGE
	CONSTRAINT PK_MSG_TBL PRIMARY KEY (MSG_ID),
	CONSTRAINT FK_MSG_RSP_ID_MSG_TBL FOREIGN KEY (MSG_RSP_ID) REFERENCES MSG_TBL(MSG_ID)
);

-- @INDEX 
-- LOOKUP BY RESPONSE ID SHOULD BE INDEXED
CREATE UNIQUE INDEX MSG_TBL_MSG_RSP_ID_IDX ON MSG_TBL(MSG_RSP_ID);

CREATE UNIQUE INDEX MSG_TBL_MSG_ID_IDX ON MSG_TBL(MSG_ID);

-- @LANGUAGE
-- PLPGSQL
--CREATE LANGUAGE PLPGSQL;

-- @PROCEDURE
-- REGISTER A MESSAGE WITH THE MTDB
CREATE OR REPLACE FUNCTION REG_MSG
(
	MSG_ID_IN	IN VARCHAR(96), -- ID OF THE MESSAGE BEING REGISTERED
	MSG_BODY_IN	IN BYTEA, -- BODY OF THE MESSAGE
	MSG_RSP_IN	IN VARCHAR(96) -- NOT NULL
) RETURNS VOID AS 
$$
BEGIN
	INSERT INTO MSG_TBL (MSG_ID, MSG_BODY) VALUES
		(MSG_ID_IN, MSG_BODY_IN);
	UPDATE MSG_TBL SET MSG_RSP_ID = MSG_ID_IN WHERE MSG_ID = MSG_RSP_IN;
	RETURN;
END;
$$ LANGUAGE plpgsql;

-- @PROCEDURE
-- REGISTER A MESSAGE WITH THE MTDB
CREATE OR REPLACE FUNCTION REG_MSG
(
	MSG_ID_IN	IN VARCHAR(96), -- ID OF THE MESSAGE BEING REGISTERED
	MSG_BODY_IN	IN BYTEA, -- BODY OF THE MESSAGE
	MSG_RSP_IN	IN VARCHAR(96), -- NOT NULL,
	SRC_IN		IN VARCHAR(256), 
	DST_IN		IN VARCHAR(256)
) RETURNS VOID AS 
$$
BEGIN
	INSERT INTO MSG_TBL (MSG_ID, MSG_BODY, MSG_SRC, MSG_DST) VALUES
		(MSG_ID_IN, MSG_BODY_IN, SRC_IN, DST_IN);
	UPDATE MSG_TBL SET MSG_RSP_ID = MSG_ID_IN WHERE MSG_ID = MSG_RSP_IN;
	RETURN;
END;
$$ LANGUAGE plpgsql;

-- @FUNCTION
-- GET THE MESSAGE FROM THE DATABASE
CREATE OR REPLACE FUNCTION GET_MSG
(
	MSG_ID_IN	IN VARCHAR(96) -- THE ID OF THE MESSAGE FOR WHICH TO FETCH
) RETURNS BYTEA AS
$$
BEGIN
	RETURN (SELECT MSG_BODY FROM MSG_TBL WHERE MSG_ID = MSG_ID_IN);
END;
$$ LANGUAGE plpgsql;


-- @FUNCTION
-- GET THE MESSAGE FROM THE DATABASE
CREATE OR REPLACE FUNCTION GET_MSG_TBL
(
	MSG_ID_IN	IN VARCHAR(96) -- THE ID OF THE MESSAGE FOR WHICH TO FETCH
) RETURNS SETOF MSG_TBL AS
$$
BEGIN
	RETURN QUERY SELECT * FROM MSG_TBL WHERE MSG_ID = MSG_ID_IN;
END;
$$ LANGUAGE plpgsql;

-- @FUNCTION
-- GET THE RESPONSE MESSAGE FROM THE DATABASE
CREATE OR REPLACE FUNCTION GET_RSP_MSG
(
	MSG_ID_IN	IN VARCHAR(96) -- THE ID OF THE MESSAGE FOR WHICH TO FETCH
) RETURNS BYTEA AS
$$
DECLARE 
	MSG_RSP_ID_VAL	VARCHAR(96);
BEGIN
	SELECT MSG_RSP_ID INTO MSG_RSP_ID_VAL FROM MSG_TBL WHERE MSG_ID = MSG_ID_IN;
	RETURN (SELECT MSG_BODY FROM MSG_TBL WHERE MSG_ID = MSG_RSP_ID_VAL);
END;
$$ LANGUAGE plpgsql;

-- @FUNCTION
-- QUERY THE STATUS OF A MESSAGE
CREATE OR REPLACE FUNCTION QRY_MSG_STATE
(
	MSG_ID_IN	IN VARCHAR(96)
) RETURNS CHAR AS
$$
BEGIN
	RETURN COALESCE(
		(SELECT CASE WHEN MSG_RSP_ID IS NULL THEN 'A' ELSE 'C' END AS STAT FROM MSG_TBL WHERE MSG_ID = MSG_ID_IN), 
		'N'
	);
END;
$$ LANGUAGE plpgsql;


-- @FUNCTION
-- QUERY MESSAGES BETWEEN DATES
CREATE OR REPLACE FUNCTION QRY_MSGS
(
	MSG_UTC_FROM_IN	IN	TIMESTAMPTZ,
	MSG_UTC_TO_IN	IN	TIMESTAMPTZ
) RETURNS TABLE
(
	MSG_ID_ID	VARCHAR(64)
) AS 
$$
BEGIN
	RETURN QUERY SELECT MSG_ID FROM MSG_TBL WHERE MSG_RSP_ID IS NOT NULL AND MSG_UTC BETWEEN MSG_UTC_FROM_IN AND MSG_UTC_TO_IN;
END
$$ LANGUAGE plpgsql;